# -*- coding: utf-8 -*-
##############################################################################
#
#    OpenERP, Open Source Management Solution
#    Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
#
#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU Affero General Public License as
#    published by the Free Software Foundation, either version 3 of the
#    License, or (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU Affero General Public License for more details.
#
#    You should have received a copy of the GNU Affero General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
##############################################################################
import xlwt
import math
import cStringIO
from .. import format_common

from openerp.osv import fields, osv
import base64
import time
from datetime import datetime, timedelta
from openerp.tools.translate import _
import logging
from babel.numbers import format_decimal
_logger = logging.getLogger(__name__)
from xlwt import *


class param_payable_aging_report(osv.osv_memory):
    _name = 'param.payable.aging.report'
    _description = 'Param Payable Aging Report'
    _columns = {
        'report_type': fields.char('Report Type', size=128, invisible=True,required=True),
        'supp_selection': fields.selection([('supplier', 'Supplier Only')],'Supplier Selection', required=True),
        'supplier_search_vals': fields.selection([('code','Supplier Code'),('name', 'Supplier Name')],'Supplier Search Values', required=True),
        'filter_selection': fields.selection([('all_vall','All'),('def','Default'),('input', 'Input'),('selection','Selection')],'Supp Filter Selection', required=True),
        'partner_default_from':fields.many2one('res.partner', 'Supplier From', domain=[('supplier','=',True)], required=False),
        'partner_default_to':fields.many2one('res.partner', 'Supplier To', domain=[('supplier','=',True)], required=False),
        'partner_input_from': fields.char('Supplier From', size=128),
        'partner_input_to': fields.char('Supplier To', size=128),
        'partner_ids' :fields.many2many('res.partner', 'report_payable_aging_rel', 'report_id', 'partner_id', 'Supplier', domain=[('supplier','=',True)]),
        'date_to': fields.date("Age Reference Date", required=True),
        'data': fields.binary('Exported CSV', readonly=True),
        'filename': fields.char('File Name',size=64),
        'target_move': fields.selection([('posted', 'All Posted Entries'),
                                        ('all', 'All Entries'),
                                        ], 'Target Moves', required=True),   
    }

    _defaults = {
        'date_to': lambda *a: time.strftime('%Y-%m-%d'),
        'report_type' : 'payable',
        'supp_selection': 'supplier',
        'supplier_search_vals': 'code',
        'filter_selection': 'all_vall',
        'target_move': 'all'
    }

    def _build_contexts(self, cr, uid, ids, data, report_type, context=None):
        if context is None:
            context = {}
        result = {}
        res_partner_obj = self.pool.get('res.partner')
        account_journal_obj = self.pool.get('account.journal')
        period_obj = self.pool.get('account.period')
        result['date_to'] = data['form']['date_to']
        result['date_to_header'] = data['form']['date_to'] or False
        qry_supp = ''
        val_part = []
        qry_jour = ''
        val_jour = []
        
        partner_ids = False
        journal_ids = False
        
        if report_type == 'receivable':
            data_search = data['form']['cust_search_vals']
            result['cust_selection'] = 'Customer'
            qry_supp = 'customer = True'
            val_part.append(('customer', '=', True))
        elif report_type == 'payable':
            data_search = data['form']['supplier_search_vals']
            if data['form']['supp_selection'] == 'supplier':
                result['supp_selection'] = 'Supplier'
                qry_supp = 'supplier = True'
                val_part.append(('supplier', '=', True))
                
        result['target_move'] = data['form']['target_move'] or False                
        partner_default_from = data['form']['partner_default_from'] or False
        partner_default_to = data['form']['partner_default_to'] or False
        partner_input_from = data['form']['partner_input_from'] or False
        partner_input_to = data['form']['partner_input_to'] or False
        partner_default_from_str = partner_default_to_str = ''
        partner_input_from_str = partner_input_to_str= ''
        
        if data_search == 'code':
            if report_type =='payable':
                result['data_search'] = 'Supplier Code'
            elif report_type =='receivable':
                result['data_search'] = 'Customer Code'
            if data['form']['filter_selection'] == 'all_vall':
                partner_ids = res_partner_obj.search(cr, uid, val_part, order='ref ASC')
            if data['form']['filter_selection'] == 'def':
                data_found = False
                if partner_default_from and res_partner_obj.browse(cr, uid, partner_default_from) and res_partner_obj.browse(cr, uid, partner_default_from).ref:
                    partner_default_from_str = res_partner_obj.browse(cr, uid, partner_default_from).ref
                    val_part.append(('ref', '>=', res_partner_obj.browse(cr, uid, partner_default_from).ref))
                if partner_default_to and res_partner_obj.browse(cr, uid, partner_default_to) and res_partner_obj.browse(cr, uid, partner_default_to).ref:
                    partner_default_to_str = res_partner_obj.browse(cr, uid, partner_default_to).ref
                    data_found = True
                    val_part.append(('ref', '<=', res_partner_obj.browse(cr, uid, partner_default_to).ref))
                if data_found:
                    result['filter_selection'] = '"' + partner_default_from_str + '" - "' + partner_default_to_str + '"'
                    partner_ids = res_partner_obj.search(cr, uid, val_part, order='ref ASC')
            elif data['form']['filter_selection'] == 'input':
                data_found = False
                if partner_input_from:
                    partner_input_from_str = partner_input_from
                    cr.execute("select ref " \
                                    "from res_partner "\
                                    "where " + qry_supp + " and " \
                                    "ref ilike '" + str(partner_input_from) + "%' " \
                                    "order by ref limit 1")
                    qry = cr.dictfetchone()
                    if qry:
                        data_found = True
                        val_part.append(('ref', '>=', qry['ref']))
                if partner_input_to:
                    partner_input_to_str = partner_input_to
                    cr.execute("select ref " \
                                    "from res_partner "\
                                    "where " + qry_supp + " and " \
                                    "ref ilike '" + str(partner_input_to) + "%' " \
                                    "order by ref desc limit 1")
                    qry = cr.dictfetchone()
                    if qry:
                        data_found = True
                        val_part.append(('ref', '<=', qry['ref']))

                result['filter_selection'] = '"' + partner_input_from_str + '" - "' + partner_input_to_str + '"'

                if data_found:
                    partner_ids = res_partner_obj.search(cr, uid, val_part, order='ref ASC')
            elif data['form']['filter_selection'] == 'selection':
                pr_ids = ''
                if data['form']['partner_ids']:
                    for pr in  res_partner_obj.browse(cr, uid, data['form']['partner_ids']):
                        pr_ids += '"' + str(pr.ref) + '",'
                    partner_ids = data['form']['partner_ids']
                result['filter_selection'] = '[' + pr_ids +']'
                
        elif data_search == 'name':
            if report_type =='payable':
                result['data_search'] = 'Supplier Name'
            elif report_type =='receivable':
                result['data_search'] = 'Customer Name'
            if data['form']['filter_selection'] == 'all_vall':
                partner_ids = res_partner_obj.search(cr, uid, val_part, order='name ASC')
            if data['form']['filter_selection'] == 'def':
                data_found = False
                if partner_default_from and res_partner_obj.browse(cr, uid, partner_default_from) and res_partner_obj.browse(cr, uid, partner_default_from).name:
                    partner_default_from_str = res_partner_obj.browse(cr, uid, partner_default_from).name
                    data_found = True
                    val_part.append(('name', '>=', res_partner_obj.browse(cr, uid, partner_default_from).name))
                if partner_default_to and res_partner_obj.browse(cr, uid, partner_default_to) and res_partner_obj.browse(cr, uid, partner_default_to).name:
                    partner_default_to_str = res_partner_obj.browse(cr, uid, partner_default_to).name
                    data_found = True
                    val_part.append(('name', '<=', res_partner_obj.browse(cr, uid, partner_default_to).name))
                if data_found:
                    result['filter_selection'] = '"' + partner_default_from_str + '" - "' + partner_default_to_str + '"'
                    partner_ids = res_partner_obj.search(cr, uid, val_part, order='name ASC')
            elif data['form']['filter_selection'] == 'input':
                data_found = False
                if partner_input_from:
                    partner_input_from_str = partner_input_from
                    cr.execute("select name " \
                                    "from res_partner "\
                                    "where " + qry_supp + " and " \
                                    "name ilike '" + str(partner_input_from) + "%' " \
                                    "order by name limit 1")
                    qry = cr.dictfetchone()
                    if qry:
                        data_found = True
                        val_part.append(('name', '>=', qry['name']))
                if partner_input_to:
                    partner_input_to_str = partner_input_to
                    cr.execute("select name " \
                                    "from res_partner "\
                                    "where " + qry_supp + " and " \
                                    "name ilike '" + str(partner_input_to) + "%' " \
                                    "order by name desc limit 1")
                    qry = cr.dictfetchone()
                    if qry:
                        data_found = True
                        val_part.append(('name', '<=', qry['name']))
                result['filter_selection'] = '"' + partner_input_from_str + '" - "' + partner_input_to_str + '"'
                if data_found:
                    partner_ids = res_partner_obj.search(cr, uid, val_part, order='name ASC')
            elif data['form']['filter_selection'] == 'selection':
                pr_ids = ''
                if data['form']['partner_ids']:
                    for pr in  res_partner_obj.browse(cr, uid, data['form']['partner_ids']):
                        pr_ids += '"' + str(pr.name) + '",'
                    partner_ids = data['form']['partner_ids']
                result['filter_selection'] = '[' + pr_ids +']'

        result['partner_ids'] = partner_ids
        
        return result

    def print_report(self, cr, uid, ids, context=None):
        if context is None:
            context = {}
        data = {}
        data['ids'] = context.get('active_ids', [])
        data['model'] = context.get('active_model', 'ir.ui.menu')
        data['form'] = self.read(cr, uid, ids, ['target_move','supp_selection', 'supplier_search_vals', 'filter_selection', 'partner_default_from','partner_default_to','partner_input_from','partner_input_to','partner_ids', \
                                                'date_to'], context=context)[0]
        for field in ['target_move','supp_selection', 'supplier_search_vals', 'filter_selection', 'partner_default_from','partner_default_to','partner_input_from','partner_input_to','partner_ids', \
                                                'date_to']:
            if isinstance(data['form'][field], tuple):
                data['form'][field] = data['form'][field][0]
        used_context = self._build_contexts(cr, uid, ids, data, 'payable',  context=context)

        return self._get_tplines_xls(cr, uid, ids, used_context, 'payable', context=context)
    
    def _get_tplines_xls(self, cr, uid, ids, data, type, context):
        form = data
        if not ids:
            ids = data['ids']
        if not ids:
            return []

        period_obj = self.pool.get('account.period')
        invoice_obj = self.pool.get('account.invoice')
        aml_obj = self.pool.get('account.move.line')
        partner_obj = self.pool.get('res.partner')
        company_obj = self.pool.get('res.company')
        user_obj = self.pool.get('res.users')
        currency_obj = self.pool.get('res.currency')
        dp_obj = self.pool.get('decimal.precision')
        
        target_move =form['target_move'] or False
        report_total = 0.00
        balance_by_cur = {}

        results = []
        results1 = []
        sign = -1

        if type == 'payable':
            sign = -1
        elif type == 'receivable':
            sign = 1
        date_to = form['date_to']
        partner_ids = form['partner_ids'] or False
        
        workbook = xlwt.Workbook()
        M_header_tstyle = format_common.font_style(position='center', bold=1, border=3, fontos='black', font_height=300)
        M_title_tstyle = format_common.font_style(position='center', bold=1, fontos='black', font_height=320)
        report_tstyle = format_common.font_style(position='left', bold=1, fontos='black', font_height=250)
        report_tstyle1 = format_common.font_style(position='left', bold=1, fontos='black', font_height=180)
        header_tstyle = format_common.font_style(position='left', bold=1, border=1, fontos='black', font_height=180)
        header_tstyle_c = format_common.font_style(position='center', bold=1, border=1, fontos='black', font_height=180)
        header_tstyle_c_Top = format_common.font_style(position='center', bold=1, border=4, fontos='black', font_height=180)
        header_tstyle_Top = format_common.font_style(position='left', bold=1, border=4, fontos='black', font_height=180)
        header_tstyle_r_Top = format_common.font_style(position='right', bold=1, border=4, fontos='black', font_height=180)
        header_tstyle_c_Bottom = format_common.font_style(position='center', bold=1, border=3, fontos='black', font_height=180)
        header_tstyle_Bottom = format_common.font_style(position='left', bold=1, border=3, fontos='black', font_height=180)
        header_tstyle_r_Bottom = format_common.font_style(position='right', bold=1, border=3, fontos='black', font_height=180)
        header_tstyle_r = format_common.font_style(position='right', bold=1, border=1, fontos='black', font_height=180)
        view_tstyle = format_common.font_style(position='left', fontos='black', font_height=180)
        view_tstyle_c = format_common.font_style(position='center', fontos='black', font_height=180)
        view_tstyle_r = format_common.font_style(position='right', fontos='black', font_height=180)
        view_tstyle_r_b = format_common.font_style(position='right', bold=1, fontos='black', font_height=180)
        other_tstyle = format_common.font_style(position='left', fontos='black', font_height=180)
        other_tstyle_c = format_common.font_style(position='center', fontos='black', font_height=180)
        other_tstyle_r = format_common.font_style(position='right', fontos='black', font_height=180)
        text_tstyle = format_common.font_style(position='left', bold=1, fontos='black', font_height=180)
        text_tstyle1 = format_common.font_style(position='left', bold=1, fontos='black', font_height=180)
        text_tstyle2 = format_common.font_style(position='left', fontos='black', font_height=220)
        text_tstyle_c = format_common.font_style(position='center', bold=1, fontos='black', font_height=180)
        text_tstyle_r = format_common.font_style(position='right', bold=1, fontos='black', font_height=180)
        text_tstyle_Top = format_common.font_style(position='left', bold=1, border=4, fontos='black', font_height=180)
        text_tstyle_Bottom = format_common.font_style(position='left', bold=1, border=3, fontos='black', font_height=180)
        text_tstyle_Top_r = format_common.font_style(position='right', bold=1, border=4, fontos='black', font_height=180)
        text_tstyle_Bottom_r = format_common.font_style(position='right', bold=1, border=3, fontos='black', font_height=180)
        text_tstyle_TopBottom = format_common.font_style(position='left', bold=1, border=2, fontos='black', font_height=180)
        text_tstyle_TopBottom_r = format_common.font_style(position='right', bold=1, border=2, fontos='black', font_height=180)
        decimal_format = easyxf(num_format_str = '0.000000')

        dp_ids = dp_obj.search(cr, uid, [('name','=','Account')])
        dp_id = dp_obj.browse(cr, uid, dp_ids[0])
    
        digits = int(math.pow(10, dp_id.digits))
        digit_output = '#,##0.' + str(digits)[1:]

        currency_format = easyxf(num_format_str = digit_output)
        
        credit_limit_format = easyxf('font: bold 1; alignment: horizontal left')
        credit_limit_format.num_format_str = digit_output
        
        currency_ttl_format = easyxf('font: bold 1')
        currency_ttl_format.num_format_str = digit_output
       
        show_partner = ''

        if type == 'payable':
            sheet = workbook.add_sheet('Payable Aging Report XLS')
            header = 'Account Payable Aging Report'
            deposit_qry = 'and not (aml.credit > 0 and aml.is_depo = True) '
            sign = -1
            if 'filter_selection' != 'all_vall':
                show_partner = 'filter_selection' in form and 'Supplier search : ' + form['filter_selection']
        elif type == 'receivable':
            sheet = workbook.add_sheet('Receivable Aging Report XLS')
            header = 'Account Receivable Aging Report'
            deposit_qry = 'and not (aml.debit > 0 and aml.is_depo = True) '
            sign = 1
            if 'filter_selection' != 'all_vall':
                show_partner = 'filter_selection' in form and 'Customer search : ' + form['filter_selection']
        
        row = 0
        sheet.row(row).height = 350
        
        company_name = user_obj.browse(cr, uid, uid).company_id.name
        sheet.write_merge(row, row, 0, 9, company_name, M_header_tstyle)
        
        row +=1
        sheet.row(row).height = 350
        sheet.write_merge(row, row, 0, 9, header, M_title_tstyle)
        
        row +=1
        sheet.write_merge(row, row, 0, 9, '')
        
        date_to_header = 'Age Reference Date : ' + form['date_to_header']
        row +=1
        sheet.write_merge(row, row, 0, 9, date_to_header, report_tstyle1)
        
        state_qry = ""
        target_move_str = "Target Move : All Entries"
        if target_move == 'posted':
            state_qry = "and am.state = 'posted' "
            target_move_str = "Target Move : All Posted Entries"
        
        row += 1  
        sheet.write_merge(row, row, 0, 9, target_move_str, report_tstyle1)    
        
        if show_partner:
            row +=1
            sheet.write_merge(row, row, 0, 8, show_partner, report_tstyle1)
   
        #Header Name
        row +=1
        sheet.write(row, 0, 'Vch. No', header_tstyle_Top)
        sheet.write(row, 1, 'TP Doc Date', header_tstyle_Top)
        sheet.write(row, 2, 'Ref No', header_tstyle_Top)
        sheet.write(row, 3, 'Currency', header_tstyle_Top)
        sheet.write(row, 4, 'Orig Amt', header_tstyle_r_Top)
        sheet.write(row, 5, 'Paid/CN', header_tstyle_r_Top)
        sheet.write(row, 6, '< 31', header_tstyle_c_Top)
        sheet.write(row, 7, '31 To 60', header_tstyle_c_Top)
        sheet.write(row, 8, '61 To 90', header_tstyle_c_Top)
        sheet.write(row, 9, 'Over 91', header_tstyle_c_Top)
        
        row +=1
        sheet.write(row, 0, 'Sales Person', header_tstyle_Bottom)
        sheet.write(row, 1, 'Due Date', header_tstyle_Bottom)
        sheet.write(row, 2, '', header_tstyle_Bottom)
        sheet.write(row, 3, 'Exrate', header_tstyle_Bottom)
        sheet.write(row, 4, 'Home', header_tstyle_r_Bottom)
        sheet.write(row, 5, 'Home', header_tstyle_r_Bottom)
        sheet.write_merge(row, row, 6, 9, '', header_tstyle_c_Bottom)
        
        #Column Width
        sheet.col(0).width = 256 * 17
        sheet.col(1).width = 256 * 15
        sheet.col(2).width = 256 * 15
        sheet.col(3).width = 256 * 12
        sheet.col(4).width = 256 * 12
        sheet.col(5).width = 256 * 12
        sheet.col(6).width = 256 * 12
        sheet.col(7).width = 256 * 12
        sheet.col(8).width = 256 * 12
        sheet.col(9).width = 256 * 12
        
        
        partner_qry = (partner_ids and ((len(partner_ids) == 1 and "AND aml.partner_id = " + str(partner_ids[0]) + " ") or "AND aml.partner_id IN " + str(tuple(partner_ids)) + " ")) or "AND aml.partner_id IN (0) "
        cr.execute(
                "select DISTINCT aml.partner_id " \
                "from account_move_line aml " \
                "left join account_move am on aml.move_id = am.id left join account_invoice ai on am.id = ai.move_id " \
                "left join account_account aa on aml.account_id = aa.id left join account_journal aj on am.journal_id = aj.id " \
                "left join res_users rs on rs.id = ai.user_id where aml.partner_id IS NOT NULL " \
#                 "and am.state IN ('draft', 'posted')  " \
                + state_qry + \
                "and aa.type = '" + type + "' " \
                "and not (aml.debit > 0 and aml.is_depo = False and aj.type in ('cash', 'bank')) " \
                + deposit_qry + \
                "and abs((aml.debit - aml.credit) - (abs(coalesce ( " \
                "(select sum(aml2.debit - aml2.credit) from account_move_line aml2 where aml2.reconcile_partial_id = aml.reconcile_partial_id and aml2.id != aml.id and aml2.date  <= '" +str(date_to) + "'), " \
                "(select sum(aml3.debit - aml3.credit) from account_move_line aml3 where aml3.reconcile_id = aml.reconcile_id and aml3.id != aml.id and aml3.date  <= '" +str(date_to) + "'), 0 " \
                ")) * (CASE WHEN (debit - credit) > 0 THEN 1 ELSE -1 END))) > 0 " \
                "And aml.date  <= '" +str(date_to) + "' "\
                "and not (aj.type in ('bank', 'cash') and aml.is_depo = False) " \
                + partner_qry)

        partner_ids_vals = []
        qry2 = cr.dictfetchall()
        if qry2:
            for r in qry2:
                partner_ids_vals.append(r['partner_id'])
        
        partner_ids_vals_qry = (len(partner_ids_vals) > 0 and ((len(partner_ids_vals) == 1 and "where id = " +  str(partner_ids_vals[0]) + " ") or "where id IN " +  str(tuple(partner_ids_vals)) + " ")) or "where id IN (0) "

        cr.execute(
                "SELECT id, name, ref, credit_limit, phone " \
                "FROM res_partner " \
                + partner_ids_vals_qry \
                + " order by name")
        qry = cr.dictfetchall()

        if qry:
            for s in qry:
                balance_by_cur_partner = {}
                partner = partner_obj.browse(cr, uid, s['id'])              
                cr.execute(
                        "select ai.payment_term as term_id, aml.id as aml_id, am.name as inv_name, aml.date as inv_date, aml.date_maturity as due_date, ai.name as inv_ref, rp.name as sales_name, aml.debit - aml.credit as home_amt, rc.id as cur_id, rc.name as currency, aml.exrate as exrate, aml.company_id as comp_id, " \
                        "abs(CASE WHEN (aml.currency_id is not null) THEN amount_currency ELSE aml.debit - aml.credit END) * (CASE WHEN (debit - credit) > 0 THEN 1 ELSE -1 END) " \
                        "as inv_amt, " \
                        "abs(coalesce ( " \
                        "(select sum(aml2.debit - aml2.credit) from account_move_line aml2 where aml2.reconcile_partial_id = aml.reconcile_partial_id and aml2.id != aml.id and aml2.date  <= '" +str(date_to) + "'), " \
                        "(select sum(aml3.debit - aml3.credit) from account_move_line aml3 where aml3.reconcile_id = aml.reconcile_id and aml3.id != aml.id and aml3.date  <= '" +str(date_to) + "'), " \
                        "0)) * (CASE WHEN (debit - credit) > 0 THEN 1 ELSE -1 END) as paid_home, " \
                        "abs(coalesce ( " \
                        "(select sum(CASE WHEN (aml4.currency_id is not null) THEN amount_currency ELSE aml4.debit - aml4.credit END) from account_move_line aml4 where aml4.reconcile_partial_id = aml.reconcile_partial_id and aml4.id != aml.id and aml4.date  <= '" +str(date_to) + "'), " \
                        "(select sum(CASE WHEN (aml5.currency_id is not null) THEN amount_currency ELSE aml5.debit - aml5.credit END) from account_move_line aml5 where aml5.reconcile_id = aml.reconcile_id and aml5.id != aml.id and aml5.date  <= '" +str(date_to) + "'), " \
                        "0)) * (CASE WHEN (debit - credit) > 0 THEN 1 ELSE -1 END) as paid " \
                        "from account_move_line aml " \
                        "left join account_move am on aml.move_id = am.id left join account_invoice ai on am.id = ai.move_id " \
                        "left join account_account aa on aml.account_id = aa.id left join account_journal aj on am.journal_id = aj.id " \
                        "left join res_currency rc on rc.id = aml.currency_id " \
                        "left join res_users rs on rs.id = ai.user_id " \
                        "left join res_partner rp on rs.partner_id = rp.id where aml.partner_id IS NOT NULL " \
#                         "and am.state IN ('draft', 'posted')  " \
                        + state_qry + \
                        "and aa.type = '" + type + "' " \
                        "And not (aml.debit > 0 and aml.is_depo = False and aj.type in ('cash', 'bank')) " \
                        "and abs((aml.debit - aml.credit) - (abs(coalesce ( " \
                        "(select sum(aml2.debit - aml2.credit) from account_move_line aml2 where aml2.reconcile_partial_id = aml.reconcile_partial_id and aml2.id != aml.id and aml2.date  <= '" +str(date_to) + "'), " \
                        "(select sum(aml3.debit - aml3.credit) from account_move_line aml3 where aml3.reconcile_id = aml.reconcile_id and aml3.id != aml.id and aml3.date  <= '" +str(date_to) + "'), 0 " \
                        ")) * (CASE WHEN (debit - credit) > 0 THEN 1 ELSE -1 END))) > 0 " \
                        "And aml.date  <= '" +str(date_to) + "' "\
                        "and not (aj.type in ('bank', 'cash') and aml.is_depo = False) " \
                        "and aml.partner_id = " + str(s['id']) + " order by aml.date")
                qry3 = cr.dictfetchall()
                val = []
                total_amt1 = total_amt2 = total_amt3 = total_amt4 = total_home_amt1 = total_home_amt2 = total_home_amt3 = total_home_amt4 = 0.00
                if qry3:
                    for t in qry3:
                        currency = ''
                        cur_id = False
                        
                        exrate = 0.00
                        if t['currency']:
                            currency = t['currency']
                            exrate = t['exrate']
                            cur_id = t['cur_id']
                        else:
                            company_currency = company_obj.browse(cr, uid, t['comp_id']).currency_id
                            currency = company_currency.name
                            exrate = company_currency.rate_silent
                            cur_id = company_currency.id

                        daysremaining = 0
                        d = datetime.strptime(t['inv_date'], '%Y-%m-%d')
                        delta = datetime.strptime(date_to, '%Y-%m-%d') - d
                        daysremaining = delta.days
                        cust_po_no = ''

                        remain_amt = (t['inv_amt'] * sign) - (t['paid'] * sign)
                        remain_home_amt = (t['home_amt'] * sign) - (t['paid_home'] * sign)

                        a_ttl_amt1 = a_ttl_amt2 = a_ttl_amt3 = a_ttl_amt4 = a_ttl_home_amt1 = a_ttl_home_amt2 = a_ttl_home_amt3 = a_ttl_home_amt4 = 0.00
                        a_ttl_amt1 = daysremaining < 31 and remain_amt or 0.00
                        a_ttl_amt2 = (daysremaining > 30 and daysremaining < 61 and remain_amt) or 0.00
                        a_ttl_amt3 = (daysremaining > 60 and daysremaining < 91 and remain_amt) or 0.00
                        a_ttl_amt4 = daysremaining > 90 and remain_amt or 0.00
                        a_ttl_home_amt1 = daysremaining < 31 and remain_home_amt or 0.00
                        a_ttl_home_amt2 = (daysremaining > 30 and daysremaining < 61 and remain_home_amt) or 0.00
                        a_ttl_home_amt3 = (daysremaining > 60 and daysremaining < 91 and remain_home_amt) or 0.00
                        a_ttl_home_amt4 = daysremaining > 90 and remain_home_amt or 0.00
                        total_amt1 += a_ttl_amt1
                        total_amt2 += a_ttl_amt2
                        total_amt3 += a_ttl_amt3
                        total_amt4 += a_ttl_amt4
                        total_home_amt1 += a_ttl_home_amt1
                        total_home_amt2 += a_ttl_home_amt2
                        total_home_amt3 += a_ttl_home_amt3
                        total_home_amt4 += a_ttl_home_amt4
                        
                        val.append({
                            'invoice_name' : t['inv_name'] or '',
                            'sales_person': t['sales_name'] or '',
                            'invoice_date' : t['inv_date'] or '',
                            'due_date' : t['due_date'] or '',
                            'ref_no' : t['inv_ref'] or '',
                            'cust_po_no' : cust_po_no or '',
                            'currency' : currency,
                            'exrate' :  exrate,
                            'orig_amt' : (t['inv_amt'] * sign) or 0.00,
                            'home_orig_amt' : (t['home_amt'] * sign) or 0.00,
                            'paid_amt' : (t['paid'] * sign) or 0.00,
                            'home_paid_amt' : (t['paid_home'] * sign) or 0.00,
                            'amt1':  a_ttl_amt1,
                            'home_amt1': a_ttl_home_amt1,
                            'amt2': a_ttl_amt2,
                            'home_amt2': a_ttl_home_amt2,
                            'amt3': a_ttl_amt3,
                            'home_amt3': a_ttl_home_amt3,
                            'amt4': a_ttl_amt4,
                            'home_amt4': a_ttl_home_amt4,
                            })
                        
                        if cur_id not in balance_by_cur_partner:
                            balance_by_cur_partner.update({cur_id : {
                                     'inv_amt' : (a_ttl_amt1 + a_ttl_amt2 + a_ttl_amt3 + a_ttl_amt4),
                                     'home_amt' : (a_ttl_home_amt1 + a_ttl_home_amt2 + a_ttl_home_amt3 + a_ttl_home_amt4),
                                     'amt1' : a_ttl_amt1,
                                     'amt2' : a_ttl_amt2,
                                     'amt3' : a_ttl_amt3,
                                     'amt4' : a_ttl_amt4,
                                     'home_amt1' : a_ttl_home_amt1,
                                     'home_amt2' : a_ttl_home_amt2,
                                     'home_amt3' : a_ttl_home_amt3,
                                     'home_amt4' : a_ttl_home_amt4,
                                     }
                                    })
                        else:
                            res_currency_grouping_part = balance_by_cur_partner[cur_id].copy()
                            res_currency_grouping_part['inv_amt'] += (a_ttl_amt1 + a_ttl_amt2 + a_ttl_amt3 + a_ttl_amt4)
                            res_currency_grouping_part['home_amt'] += (a_ttl_home_amt1 + a_ttl_home_amt2 + a_ttl_home_amt3 + a_ttl_home_amt4)
                            res_currency_grouping_part['amt1'] += a_ttl_amt1
                            res_currency_grouping_part['amt2'] += a_ttl_amt2
                            res_currency_grouping_part['amt3'] += a_ttl_amt3
                            res_currency_grouping_part['amt4'] += a_ttl_amt4
                            res_currency_grouping_part['home_amt1'] += a_ttl_home_amt1
                            res_currency_grouping_part['home_amt2'] += a_ttl_home_amt2
                            res_currency_grouping_part['home_amt3'] += a_ttl_home_amt3
                            res_currency_grouping_part['home_amt4'] += a_ttl_home_amt4
                            balance_by_cur_partner[cur_id] = res_currency_grouping_part 
                        
                        if cur_id not in balance_by_cur:
                            balance_by_cur.update({cur_id : {
                                     'inv_amt' : (a_ttl_amt1 + a_ttl_amt2 + a_ttl_amt3 + a_ttl_amt4),
                                     'home_amt' : (a_ttl_home_amt1 + a_ttl_home_amt2 + a_ttl_home_amt3 + a_ttl_home_amt4),
                                     'amt1' : a_ttl_amt1,
                                     'amt2' : a_ttl_amt2,
                                     'amt3' : a_ttl_amt3,
                                     'amt4' : a_ttl_amt4,
                                     'home_amt1' : a_ttl_home_amt1,
                                     'home_amt2' : a_ttl_home_amt2,
                                     'home_amt3' : a_ttl_home_amt3,
                                     'home_amt4' : a_ttl_home_amt4,
                                     }
                                    })
                        else:
                            res_currency_grouping = balance_by_cur[cur_id].copy()
                            res_currency_grouping['inv_amt'] += (a_ttl_amt1 + a_ttl_amt2 + a_ttl_amt3 + a_ttl_amt4)
                            res_currency_grouping['home_amt'] += (a_ttl_home_amt1 + a_ttl_home_amt2 + a_ttl_home_amt3 + a_ttl_home_amt4)
                            res_currency_grouping['amt1'] += a_ttl_amt1
                            res_currency_grouping['amt2'] += a_ttl_amt2
                            res_currency_grouping['amt3'] += a_ttl_amt3
                            res_currency_grouping['amt4'] += a_ttl_amt4
                            res_currency_grouping['home_amt1'] += a_ttl_home_amt1
                            res_currency_grouping['home_amt2'] += a_ttl_home_amt2
                            res_currency_grouping['home_amt3'] += a_ttl_home_amt3
                            res_currency_grouping['home_amt4'] += a_ttl_home_amt4
                            balance_by_cur[cur_id] = res_currency_grouping
                val = val and sorted(val, key=lambda val_res: val_res['invoice_date']) or []
                results1.append({
                    'part_name' : s['name'] or '',
                    'part_ref' : s['ref'] or '',
                    'contact_phone' : s['phone'] or '',
                    'credit_limit' : s['credit_limit'] or 0.00,
                    'total_inv' : (total_amt1 + total_amt2 + total_amt3 + total_amt4) or 0.00,
                    'total_home' : (total_home_amt1 + total_home_amt2 + total_home_amt3 + total_home_amt4) or 0.00,
                    'total_amt1' : total_amt1 or  0.00,
                    'total_home_amt1' : total_home_amt1 or 0.00,
                    'total_amt2' : total_amt2 or 0.00,
                    'total_home_amt2' : total_home_amt2 or 0.00,
                    'total_amt3' : total_amt3 or 0.00,
                    'total_home_amt3' : total_home_amt3 or 0.00,
                    'total_amt4' : total_amt4 or 0.00,
                    'total_home_amt4' : total_home_amt4 or 0.00,
                    'val_ids': val,
                    'balance_by_cur_partner' : balance_by_cur_partner,
                    })

        results1 = results1 and sorted(results1, key=lambda val_res: val_res['part_name']) or []
        
        for rs1 in results1:
            row += 1
            sheet.write(row, 0, str(rs1['part_ref']), text_tstyle_c)
            sheet.write_merge(row, row, 1, 4, str(rs1['part_name']), text_tstyle1)
            sheet.write_merge(row, row, 5, 6, 'Tel : ' + str(rs1['contact_phone']) , text_tstyle1)
            sheet.write_merge(row, row, 7, 8, 'Credit Limit: ', text_tstyle_r)
            sheet.write(row, 9, rs1['credit_limit'] or 0.00, credit_limit_format)
            
            total_home_amt = 0
            for rs2 in rs1['val_ids']:
                row += 1
                sheet.write(row, 0, str(rs2['invoice_name']), view_tstyle)
                sheet.write(row, 1, str(rs2['invoice_date']), view_tstyle)
                sheet.write(row, 2, str(rs2['ref_no']), view_tstyle)
                sheet.write(row, 3, str(rs2['currency']), view_tstyle) #cur_name
                sheet.write(row, 4, rs2['orig_amt'], currency_format)
                sheet.write(row, 5, rs2['paid_amt'], currency_format)
                sheet.write(row, 6, rs2['amt1'], currency_format)
                sheet.write(row, 7, rs2['amt2'], currency_format)
                sheet.write(row, 8, rs2['amt3'], currency_format)
                sheet.write(row, 9, rs2['amt4'], currency_format)
                
                row += 1
                sheet.write(row, 0, str(rs2['sales_person']), view_tstyle)
                sheet.write(row, 1, str(rs2['due_date']), view_tstyle)
                sheet.write(row, 2, str(rs2['cust_po_no']), view_tstyle)
                sheet.write(row, 3, rs2['exrate'], decimal_format) #exrate
                sheet.write(row, 4, rs2['home_orig_amt'], currency_format)
                sheet.write(row, 5, rs2['home_paid_amt'], currency_format)
                sheet.write(row, 6, rs2['home_amt1'], currency_format)
                sheet.write(row, 7, rs2['home_amt2'], currency_format)
                sheet.write(row, 8, rs2['home_amt3'], currency_format)
                sheet.write(row, 9, rs2['home_amt4'], currency_format)
                
            a_balance_by_cur_part = rs1['balance_by_cur_partner']
            result_currency_part = []
            for item_part in a_balance_by_cur_part.items():
                result_currency_part.append({
                    'cur_name' : currency_obj.browse(cr, uid, item_part[0]).name,
                    'total_inv' : item_part[1]['inv_amt'],
                    'total_home' : item_part[1]['home_amt'],
                    'amt1' : item_part[1]['amt1'],
                    'amt2' : item_part[1]['amt2'],
                    'amt3' : item_part[1]['amt3'],
                    'amt4' : item_part[1]['amt4'],
                    'home_amt1' : item_part[1]['home_amt1'],
                    'home_amt2' : item_part[1]['home_amt2'],
                    'home_amt3' : item_part[1]['home_amt3'],
                    'home_amt4' : item_part[1]['home_amt4'],
                })
             
            result_currency_part = result_currency_part and sorted(result_currency_part, key=lambda val_res: val_res['cur_name']) or []
            
            if rs1['part_ref'] == '' :
                sheet.write_merge(row+1, row+1, 0, 2, 'Total by Currency ' + str(rs1['part_name']) + ' :', text_tstyle1)
            else :
                sheet.write_merge(row+1, row+1, 0, 2, 'Total by Currency (' + str(rs1['part_ref']) + ') ' + str(rs1['part_name']) + ' :', text_tstyle1)
                
            for rs_curr_part in result_currency_part:

                row += 1
                sheet.write(row, 3, str(rs_curr_part['cur_name']), text_tstyle)
                sheet.write(row, 4, rs_curr_part['total_inv'], currency_ttl_format)
                sheet.write(row, 6, rs_curr_part['amt1'], currency_ttl_format)
                sheet.write(row, 7, rs_curr_part['amt2'], currency_ttl_format)
                sheet.write(row, 8, rs_curr_part['amt3'], currency_ttl_format)
                sheet.write(row, 9, rs_curr_part['amt4'], currency_ttl_format)
                 
                row += 1
                sheet.write_merge(row, row, 0, 1, '')
                sheet.write(row, 3, 'Home', text_tstyle)
                sheet.write(row, 4, rs_curr_part['total_home'], currency_ttl_format)
                sheet.write(row, 6, rs_curr_part['home_amt1'], currency_ttl_format)
                sheet.write(row, 7, rs_curr_part['home_amt2'], currency_ttl_format)
                sheet.write(row, 8, rs_curr_part['home_amt3'], currency_ttl_format)
                sheet.write(row, 9, rs_curr_part['home_amt4'], currency_ttl_format)

            row += 1
            sheet.row(row).height = 100
            sheet.write_merge(row, row, 0, 9, '', header_tstyle_c_Bottom)
                
        result_currency = []
        currency_obj    = self.pool.get('res.currency')
        for item in balance_by_cur.items():
            result_currency.append({
                'cur_name' : currency_obj.browse(cr, uid, item[0]).name,
                'total_inv' : item[1]['inv_amt'],
                'total_home' : item[1]['home_amt'],
                'amt1' : item[1]['amt1'],
                'amt2' : item[1]['amt2'],
                'amt3' : item[1]['amt3'],
                'amt4' : item[1]['amt4'],
                'home_amt1' : item[1]['home_amt1'],
                'home_amt2' : item[1]['home_amt2'],
                'home_amt3' : item[1]['home_amt3'],
                'home_amt4' : item[1]['home_amt4'],
            })

        result_currency = result_currency and sorted(result_currency, key=lambda val_res: val_res['cur_name']) or []
        _total_home = 0
        _total_amt1 = _total_amt2 = _total_amt3 = _total_amt4 = 0.00

        row += 1
        sheet.write_merge(row, row, 0, 1, 'Report Total By Currency', text_tstyle1)
        for rs_curr in result_currency:
            _total_home += rs_curr['total_home'] or 0.00
            _total_amt1 += rs_curr['home_amt1'] or 0.00
            _total_amt2 += rs_curr['home_amt2'] or 0.00
            _total_amt3 += rs_curr['home_amt3'] or 0.00
            _total_amt4 += rs_curr['home_amt4'] or 0.00
            
            row += 1
            sheet.write(row, 3, rs_curr['cur_name'], text_tstyle)
            sheet.write(row, 4, rs_curr['total_inv'], currency_format)
            sheet.write(row, 6, rs_curr['amt1'], currency_format)
            sheet.write(row, 7, rs_curr['amt2'], currency_format)
            sheet.write(row, 8, rs_curr['amt3'], currency_format)
            sheet.write(row, 9, rs_curr['amt4'], currency_format)
             
            row += 1
            sheet.write_merge(row, row, 0, 1, '')
            sheet.write(row, 3, 'Home', text_tstyle)
            sheet.write(row, 4, rs_curr['total_home'], currency_format)
            sheet.write(row, 6, rs_curr['home_amt1'], currency_format)
            sheet.write(row, 7, rs_curr['home_amt2'], currency_format)
            sheet.write(row, 8, rs_curr['home_amt3'], currency_format)
            sheet.write(row, 9, rs_curr['home_amt4'], currency_format)
            
        row += 1
        sheet.row(row).height = 100
        sheet.write_merge(row, row, 0, 9, '', header_tstyle_Bottom)
            
        row += 1
        sheet.write_merge(row, row, 0, 1, '')
        sheet.write(row, 3, 'Total Home', text_tstyle)
        sheet.write(row, 4, _total_home, currency_ttl_format)
        sheet.write(row, 5, '', text_tstyle)
        sheet.write(row, 6, _total_amt1, currency_ttl_format)
        sheet.write(row, 7, _total_amt2, currency_ttl_format)
        sheet.write(row, 8, _total_amt3, currency_ttl_format)
        sheet.write(row, 9, _total_amt4, currency_ttl_format)
             
        row += 1
        sheet.write(row, 0, 'End of Report ', text_tstyle)
            
        stream = cStringIO.StringIO()
        workbook.save(stream)
        
        if type == 'payable':
            cr.execute(""" DELETE FROM output""")
            attach_id = self.pool.get('output').create(cr, uid, {'name':'Payable Aging Report.xls', 'xls_output':base64.encodestring(stream.getvalue())})
            return {
                    'name': _('Notification'),
                    'context': context,
                    'view_type': 'form',
                    'view_mode': 'form',
                    'res_model': 'output',
                    'res_id':attach_id,
                    'type': 'ir.actions.act_window',
                    'target':'new'
                    }  
        elif type == 'receivable':
            cr.execute(""" DELETE FROM output""")
            attach_id = self.pool.get('output').create(cr, uid, {'name':'Receivable Aging Report.xls', 'xls_output':base64.encodestring(stream.getvalue())})
            return {
                    'name': _('Notification'),
                    'context': context,
                    'view_type': 'form',
                    'view_mode': 'form',
                    'res_model': 'output',
                    'res_id':attach_id,
                    'type': 'ir.actions.act_window',
                    'target':'new'
                    }  

    def generate(self, cr, uid, ids, context=None):
        if context is None:
            context = {}
        datas = {'ids': context.get('active_ids', [])}
        datas['model'] = 'param.payable.aging.report'
        datas['form'] = self.read(cr, uid, ids)[0]
        return {
            'type': 'ir.actions.report.xml',
            'report_name': 'aging.report_landscape',
            'datas': datas,
        }
